﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SQLite;
using System.IO;
using ALDIOrganizer.Properties;

namespace ALDIOrganizer
{
    public static partial class SQLiteAccess
    {
        /// <summary>
        /// Returns a filtered array of DataRows meeting specified requirements
        /// </summary>
        /// <param Name="source">The input data (to be filtered out)</param>
        /// <param Name="Start"></param>
        /// <param Name="Finish"></param>
        /// <param Name="startDateFIELD"></param>
        /// <param Name="endDateFIELD"></param>
        /// <param Name="flagFIELDS"></param>
        /// <returns></returns>
        static DataRow[] GetStatuses(
            DataRow[] pSourceRows,
            DateTime pStart,
            DateTime pEnd,
            string pStartDateFieldName,
            string pEndDateFieldName)
        {
            #region Initial checks
            if (pStart.Date > pEnd.Date)
            {
                throw new ArgumentException("Start date cannot be greater (later) than the end date!");
            }
            if (pSourceRows == null ||
                pSourceRows.Length == 0)
            {
                return null;
            }
            #endregion

            IEnumerable<DataRow> query;

            /* Shall we take into consideration When the retrieved records "expire"? Do they have some "until" field?
             * For example, holidays have. They always Finish On a specified date.
             * On the other hand - contracts don't. They are, by default, ever-lasting.
             * 
             * If the caller doesn't want to specify an "Finish date" field, they should just pass an Empty string. */
            bool endDateSpecified = pEndDateFieldName.Trim() != String.Empty;

            if (endDateSpecified)
            {
                query = from row in pSourceRows.AsEnumerable()
                        where row.Field<DateTime>(pStartDateFieldName) <= pEnd.Date &
                        (row.Field<DateTime?>(pEndDateFieldName) == null |
                        row.Field<DateTime?>(pEndDateFieldName) >= pStart.Date)
                        /* There may be an Finish date field ON THE TABLE (e.g. requests do have
                         * an "expiry date", whereas contracts do not), but it doesn't mean
                         * that it will have a value for every single row. */
                        orderby row.Field<DateTime>(pStartDateFieldName)
                        select row;
            }
            else
            {
                var preexistent = (from row in pSourceRows.AsEnumerable()
                                   where row.Field<DateTime>(pStartDateFieldName) <= pStart.Date
                                   select row).LastOrDefault();

                query = from row in pSourceRows.AsEnumerable()
                        where row.Field<DateTime>(pStartDateFieldName) > pStart.Date
                        & row.Field<DateTime>(pStartDateFieldName) <= pEnd.Date
                        orderby row.Field<DateTime>(pStartDateFieldName)
                        select row;

                if (preexistent != null)
                {
                    // inserts the "preexisting" one BEFORE the query results
                    query = new[] { preexistent }.Concat(query);
                }
            }


            return query.ToArray();

        }

        internal static KeyValuePair<bool[], Contract[]> GetContractsForWeek(Int64 pEmployeeID, DateTime pMonday)
        {
            var bits = new bool[8];
            var contracts = new Contract[7];

            var source = GetStatuses(GetContracts(pEmployeeID), pMonday, pMonday.AddDays(6), "date", String.Empty);
            bool any = false;

            if (source != null) if (source.Length > 0)
                    for (int day = 0; day < 7; day++)
                    {
                        var date = pMonday.AddDays(day);
                        var row = (from conrow in source.AsEnumerable()
                                   where conrow.Field<DateTime>("date") <= date
                                   select conrow).LastOrDefault();
                        if (row == null)
                        {
                            continue;
                        }
                        var c = new Contract(row);
                        contracts[day] = c;
                        bits[day] = c.Hired;
                        if (bits[day]) any = true;
                    }

            bits[7] = any;
            return new KeyValuePair<bool[], Contract[]>(bits, contracts);
        }

        internal static KeyValuePair<bool[], Holiday[]> GetHolidaysForWeek(Int64 pEmployeeID, DateTime pMonday, bool pIncludeSick)
        {
            var bits = new bool[8];
            var holidays = new Holiday[7];

            var source = GetStatuses(GetHolidayss(pEmployeeID), pMonday, pMonday.AddDays(6), "startDate", "endDate");
            var any = false;

            if (source != null &&
                source.Length > 0)
            {
                for (var day = 0; day < 7; day++)
                {
                    DateTime date = pMonday.AddDays(day);
                    DataRow row;
                    if (pIncludeSick)
                    {
                        row = (from holrow in source.AsEnumerable()
                                where holrow.Field<DateTime>("startDate") <= date
                                & holrow.Field<DateTime>("endDate") >= date
                                & !holrow.Field<bool>("pending")
                                select holrow).LastOrDefault();
                    }
                    else
                    {
                        row = (from holrow in source.AsEnumerable()
                                where holrow.Field<DateTime>("startDate") <= date
                                & holrow.Field<DateTime>("endDate") >= date
                                & !holrow.Field<bool>("pending")
                                & holrow.Field<Int64>("type") != 3
                                select holrow).LastOrDefault();
                    }
                    if (row == null)
                    {
                        continue;
                    }
                    var h = new Holiday(row);
                    holidays[day] = h;
                    bits[day] = true; // if row is null it just remains false (which is default value for bools)
                    if (bits[day]) any = true;
                }
            }
            bits[7] = any;
            return new KeyValuePair<bool[], Holiday[]>(bits, holidays);
        }

        internal static KeyValuePair<bool[], List<Request>[]> GetRequestsForWeek(Int64 pEmployeeID, DateTime pMonday)
        {
            var _bits = new bool[8];
            var _requests = new List<Request>[7];
            var _source = GetStatuses(GetRequests(pEmployeeID),
                pMonday,
                pMonday.AddDays(6),
                "startDate",
                "endDate");
            bool _any = false;

            if (_source != null)
            {
                if (_source.Length > 0)
                {
                    for (int day = 0; day < 7; day++)
                    {
                        DateTime _date = pMonday.AddDays(day);

                        var _to_requests = from _reqrow in _source.AsEnumerable()
                                           select new { request = new Request(_reqrow) };
                        _requests[day] = (from _each in _to_requests
                                          where _each.request.AppliesOnDay(_date, true)
                                          select _each.request).ToList();
                        if (_requests[day].Count <= 0)
                        {
                            continue;
                        }
                        _any = true;
                        _bits[day] = true;
                    }
                }
            }
            _bits[7] = _any;
            return new KeyValuePair<bool[], List<Request>[]>(_bits, _requests);
        }

        internal static DataRow GetFutureContract(Int64 pEmployeeID, DateTime pDate)
        {
            var query = from contract in _dataset.Tables[1].AsEnumerable()
                        where contract.Field<Int64>("employeeID") == pEmployeeID &&
                        contract.Field<DateTime>("date") > pDate &&
                        contract.Field<bool>("hired")
                        select contract;
            return query.FirstOrDefault();
        }

        /// <summary>
        /// Returns the number of actual contracts for an employee, not counting the "negative" contracts (lay-offs)
        /// </summary>
        /// <param name="pEmployeeID"></param>
        /// <remarks>
        /// This method is needed when our user is about to delete a contract.
        /// No employee can be left in the database without any contracts whatsoever.
        /// </remarks>
        internal static int HiredContractsCount(Int64 pEmployeeID)
        {
            return (from contract in GetContracts(pEmployeeID)
                    where new Contract(contract).Hired
                    select contract).Count();
        }

        /// <summary>
        /// gets GetTotal pDays of holidays booked for the given _employee in the given year
        /// </summary>
        /// <param Name="EmpID">_employee'pShift id in the CurrentFile</param>
        /// <param Name="year">year in question</param>
        /// <returns>number of WORKING pDays (not GetTotal pDays!</returns>
        /// <remarks>one week is 5 working pDays, hence if someone - for example -
        /// booked 14 pDays of holidays (date-Sunday + another date-Sunday),
        /// it effectively counts as 10 pDays of holidays.</remarks>
        internal static int GetUsedHoliday(Int64 EmpID, int pYear)
        {
            /* paternal or unpaid leave is not counted as _holiday Allowance.
             * only regular _holiday is.
             * if something changes, however - just AddAnotherRowString more HolidayType values
             * to the array below. */
            var typesToBeCounted = new Int64[] { (int)HolidayType.regular };

            var holidays = (from hol in _dataset.Tables[2].AsEnumerable()
                            where (hol.Field<Int64>("employeeID") == EmpID)
                            && (!hol.Field<bool>("pending"))
                            && (hol.Field<DateTime>("startDate").Year == pYear)
                            && ((hol.Field<Int64>("type") == (int)HolidayType.regular)
                            || (typesToBeCounted.Contains((int)hol.Field<Int64>("type"))))
                            select ((hol.Field<DateTime>("endDate") - hol.Field<DateTime>("startDate")).Days + 1)).Sum();
            /* note: we add 1, because... well imagine if someone booked ONE DAY of_holiday, 
             * for example 3rd of May.
             * startDate = 03.05, endDate = 03.05, endDate - startDate = 0 days... whereas it's actually 1 day */
            return ((holidays / 7) * 5) + (holidays % 7);
        }

        /// <summary>
        /// Calculates
        /// </summary>
        /// <param Name="startPeriod"></param>
        /// <param Name="endPeriod"></param>
        /// <param Name="values"></param>
        /// <remarks>
        /// Imagine that an _employee joined the company On 1st of April (he didn't work first three months of the year).
        /// His _holiday Allowance (On the April _contract) was 20 pDays, but in October it was increased to 30.
        /// How much _holiday is he *really* entitled in this year?
        /// He spent 3 months with an Allowance of 0 pDays (not being an _employee at all).
        /// Then 3 months with an Allowance of 20 pDays.
        /// Then - finally - 3 months with an Allowance of 30 pDays.
        /// The average is 16 pDays.
        /// </remarks>
        /// <returns></returns>
        internal static KeyValuePair<int, bool> ComputeAverage(
            DateTime startPeriod,
            DateTime endPeriod,
            KeyValuePair<DateTime, int>[] values)
        {
            foreach (var kv in values.Where(kv =>
                kv.Key > endPeriod))
            {
                throw new ArgumentException(
                    "CalculateAverage: one of the values' dates greater than endPeriod! ("
                    + kv.Key.ToString()
                    + " > "
                    + endPeriod.Date.ToString());
            }
            var result = 0;
            if (values.Length == 0)
            {
                return new KeyValuePair<int, bool>(0, false);
            }
            if (values[0].Key != startPeriod)
            {
                if (values[0].Key > startPeriod)
                {
                    var blank = new KeyValuePair<DateTime, int>(startPeriod, 0);
                    values = (new[] { blank }).
                        Concat(values).ToArray();
                }
                if (values[0].Key < startPeriod)
                {
                    values[0] = new KeyValuePair<DateTime, int>(
                        startPeriod,
                        values[0].Value);
                }
            }

            if (values.Length == 1)
            {
                return new KeyValuePair<int, bool>(
                    values[0].Value,
                    false); // values.Key must be startPeriod or earlier, and there'pShift no later contracts
            }

            long total_value = 0;
            for (int i = 0; i < values.Length; i++)
            {
                int period_length;

                if (i == values.Length - 1)
                {
                    period_length = (endPeriod - values[i].Key).Days;
                }
                else
                {
                    period_length = (values[i + 1].Key - values[i].Key).Days;
                }
                total_value += period_length * values[i].Value;
            }           
            result = (int)(total_value / (endPeriod - startPeriod).Days);
            return new KeyValuePair<int, bool>(
                result,
                true);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param Name="EmpID"></param>
        /// <param Name="year"></param>
        /// <remarks>
        /// 
        /// </remarks>
        /// <returns></returns>
        internal static KeyValuePair<int, bool> GetHolidayAllowances(Int64 EmpID, int year)
        {
            var contracts = GetContracts(EmpID);
            var yearstart = new DateTime(year, 1, 1);
            var yearend = new DateTime(year, 12, 31);
            var statuses = GetStatuses(contracts, yearstart, yearend, "date", String.Empty);
            var values = from contract in statuses
                         orderby contract.Field<DateTime>("date")
                         select new KeyValuePair<DateTime, int>(contract.Field<DateTime>("date"), (int)contract.Field<Int64>("holidayAllowance"));

            return ComputeAverage(yearstart, yearend, values.ToArray());
        }

        internal static KeyValuePair<int, bool> ComputeWorkingHoursForWeek(Int64 EmpID, DateTime monday)
        {
            var contracts = GetContracts(EmpID);
            var sunday = monday.AddDays(6);
            var statuses = GetStatuses(contracts, monday, sunday, "date", String.Empty);
            var values = from contract in statuses
                         orderby contract.Field<DateTime>("date")
                         select new KeyValuePair<DateTime, int>(
                             contract.Field<DateTime>("date"),
                             (int)contract.Field<Int64>("contractHRS"));

            return ComputeAverage(monday, sunday, values.ToArray());
        }

        internal static int GetPendingHolidaysCount(int year)
        {
            return (from hol in _dataset.Tables[2].AsEnumerable()
                    where hol.Field<bool>("pending")
                    & hol.Field<DateTime>("startDate").Year == year
                    select hol).Count();
        }


        /// <summary>
        /// returns oldest (smallest) and latest (biggest) year holidays
        /// </summary>
        /// <returns>
        /// Key     = GetMinutes (oldest year)
        /// Value   = max (newest year)
        /// </returns>
        internal static KeyValuePair<int, int> GetHolidaysYearsMinMax()
        {
            int min;
            int max;
            var years = from holiday in _dataset.Tables[2].AsEnumerable()
                        select holiday.Field<DateTime>("startDate").Year;
            if (years.Count() > 0)
            {
                min = Convert.ToInt32(years.Min());
                max = Convert.ToInt32(years.Max());
            }
            else
            {
                min = 0;
                max = 0;
            }
            return new KeyValuePair<int, int>(min, max);
        }

        internal static List<int> GetHolidayYears()
        {
            var current = DateTime.Now.Year;
            var yearSpan = GetHolidaysYearsMinMax();
            /* we are retrieving the oldest and the newest year of holidays from the Dataset (above),
             * but it might not include the current year, whereas it has to.
             * 
             * if there is no holidays in the CurrentFile, GetHolidaysYearsMinMax returns (0, 0).
             * in this case we need to ignore this Value, which is done by comparing current vs current
             * if that'pShift the case */
            var min = Math.Min((yearSpan.Key != 0) ? yearSpan.Key : current, current);
            var max = Math.Max((yearSpan.Value != 0) ? yearSpan.Value : current, current);
            max += Settings.Default.HOLIDAY_years_ahead;

            var years = new List<int>();
            for (var year = min; year <= max; year++)
            {
                years.Add(year);
            }
            return years;
        }

        internal static Int64[] FindOverlappingHolidays(
            Int64 pHolidayID,
            Int64 pEmpID,
            DateTime start,
            DateTime end)
        {
            var overlapping = from hol in _dataset.Tables[2].AsEnumerable()
                              where hol.Field<Int64>("employeeID") == pEmpID &
                              !hol.Field<bool>("pending") &
                              hol.Field<Int64>("ID") != pHolidayID &
                              ((hol.Field<DateTime>("startDate") > start.Date) & (hol.Field<DateTime>("startDate") < end.Date) |
                              (hol.Field<DateTime>("endDate") > start.Date) & (hol.Field<DateTime>("endDate") < end.Date))
                              select hol.Field<Int64>("ID");

            return overlapping.ToArray();
        }

        internal static void CalculateAllColumns()
        {
            CalculateColumns(
                DateTime.Now,
                new[] { 0, 1, 2, 3 },
                -1);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param Name="date">date to be assumed as the current one (people employed at this specific date will be marked as working, etc.)</param>
        /// <param Name="tables">which datatables require descriptions</param>
        /// <param Name="EmployeeID">which _employee ? id from the CurrentFile. -1 if everyone.</param>
        internal static void CalculateColumns(DateTime date, int[] tables, Int64 empID)
        {
            var employees = _dataset.Tables[0];

            if (employees.Rows.Count == 0)
            {
                return; // no emps_from_database? no work to do
            }
            /* NOTE: yes, there might (theoretically) be some contracts or holidays, but it would make no sense
             * since they'd be orphaned records. this would only imply the CurrentFile is corrupt.
             * WHOSE contracts? WHOSE holidays? */

            // we're reassured there ARE emps_from_database.
            
            var ids = GetEmployeeIDs(empID);

            if (tables.Contains(0) || tables.Contains(1))
            {
                // if EmployeeID is -1 we will iterate through all the emps_from_database.
                // if >= 0 - just the one with the id as passed in the argument.


                foreach (Int64 id in ids)
                {
                    var employee = GetEmployee(id).AsEmployee();
                    var statuses = GetStatuses(
                        GetContracts(employee.ID),
                        date,
                        date.AddDays(6),
                        "date",
                        String.Empty);
                    Contract contract = null;
                    if (statuses.Length > 0)
                    {
                        contract = new Contract(statuses[0]);
                        employee.currentStatus = contract.Hired ?
                            Resources.EMPLOYEE_STATUS_Employed :
                            Resources.EMPLOYEE_STATUS_NotEmployed;                        
                    }
                    else
                    {
                        var closestFutureContract = GetFutureContract(id, date.AddDays(6));
                        if (closestFutureContract != null &&
                            new Contract(closestFutureContract).Hired)
                        {
                            employee.currentStatus = Resources.EMPLOYEE_STATUS_Starting; // anymore
                            contract = new Contract(closestFutureContract);
                        }
                        else
                        {
                            employee.currentStatus = Resources.EMPLOYEE_STATUS_NotEmployed; // anymore                            
                        }
                    }

                    if (contract != null)
                    {
                        employee.TakeContract(contract);
                    }
                    employee.displayColumn = employee.FullDescription();
                    employee.AcceptChanges();
                }
            }

            // contracts
            if (tables.Contains(1))
            {
                foreach (var id in ids)
                {
                    Contract.DescribeContracts(Contract.ToContracts(GetContracts(id)));
                }
            }


            // holidays
            if (tables.Contains(2))
            {
                foreach (var id in ids)
                {
                    Holiday.DescribeHolidays(Holiday.ToHolidays(GetHolidayss(id)));
                }
            }
            employees.AcceptChanges();
        }



        internal static bool HasAnyRequests(Int64 pWho)
        {
            return (from request in _dataset.Tables[3].AsEnumerable()
                    where request.Field<Int64>("employeeID") == pWho
                    select request).Any();
        }
    }
}
